//
//  BSQLConfig.h
//  readNovels
//
//  Created by wulanzhou on 2017/2/27.
//  Copyright © 2017年 wulanzhou. All rights reserved.
//

#ifndef BSQLConfig_h
#define BSQLConfig_h

/******************create table sql*****************/

#define book_table_sql @"create table if not exists book (\
id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,\
name VARCHAR(50) NOT NULL ,\
author VARCHAR(20),\
price DOUBLE,\
publisher VARCHAR(100),\
isbn VARCHAR(20),\
status INTEGER,\
ctime DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),\
mtime DATETIME)"

#define catalog_table_sql @"create table if not exists catalog (\
id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,\
book_id INTEGER NOT NULL ,\
serial_number VARCHAR(20) NOT NULL ,\
name VARCHAR(50) NOT NULL ,\
page_url VARCHAR(200),\
sort INTEGER,\
memo VARCHAR(200),\
ctime DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),\
mtime DATETIME)"

#define chapter_table_sql @"create table if not exists chapter (\
catalog_id INTEGER PRIMARY KEY ,\
title VARCHAR(50),\
content TEXT,\
finished REAL DEFAULT 0,\
sort INTEGER,\
memo VARCHAR(200),\
ctime DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),\
mtime DATETIME)"

/******************insert table sql****************************/

#define insert_book_sql     @"insert into book(name,author,price,publisher,isbn,status,ctime,mtime) values(?,?,?,?,?,?,?,?)"
#define insert_catalog_sql  @"insert into catalog(book_id,serial_number,name,page_url,sort,memo,ctime,mtime) values(?,?,?,?,?,?,?,?)"
#define insert_chapter_sql  @"insert into chapter(catalog_id,title,content,finished,sort,memo,ctime,mtime) values(?,?,?,?,?,?,?,?)"

/******************query table sql****************************/

#define query_all_book    @"select * from book"
#define query_id_book     @"select * from book where id = ?"
#define query_status_book @"select * from book where status = ?"
#define query_name_book @"select * from book where `name` = ?"

#define query_all_catalog         @"select * from catalog order by sort asc"
#define query_id_catalog          @"select * from catalog where id = ?"
#define query_unfinished_catalog  @"select c.* from catalog   c inner  join chapter g on c.id=g.catalog_id and g.finished=0 where c.book_id=?\
 union\
 select * from catalog where book_id=? and not exists(select 1 from chapter where catalog_id=catalog.id)"

#define query_all_chapter             @"select * from chapter order by sort asc"
#define query_finished_chapter        @"select * from chapter where finished = ?"
#define query_finished_book_chapter   @"select * from chapter where finished = ? and catalog_id in (select id from catalog where book_id=?)"
#define query_catalog_id_chapter      @"select * from chapter where catalog_id = ?"
#define query_book_id_chapter         @"select * from chapter where catalog_id in(SELECT id FROM catalog where book_id=?) order by sort"

/******************update table sql****************************/

#define update_book_status      @"update book set status = ?,mtime = ? where id = ?"
#define update_chapter_status   @"update chapter set finished = ?,mtime = ? where catalog_id = ?"
#define update_chapter_content  @"update chapter set content = ?,finished = ?,mtime = ? where catalog_id = ?"
#define update_catalog_url      @"update catalog set page_url=? where id=?"

/******************delete table sql****************************/
#define delete_catalog_book_id      @"delete from catalog where book_id=?"
#define delete_chapter_catalog_id   @"delete from chapter where catalog_id>=?"

#endif /* BSQLConfig_h */
